SET NOCOUNT ON
GO

USE master
GO
if exists (select * from sysdatabases where name='UserDefinedForms')
		drop database UserDefinedForms
go

DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

EXECUTE (N'CREATE DATABASE UserDefinedForms
  ON PRIMARY (NAME = N''UserDefinedForms'', FILENAME = N''' + @device_directory + N'userdefinedform.mdf'')
  LOG ON (NAME = N''UserDefinedForms_log'',  FILENAME = N''' + @device_directory + N'userdefinedform.ldf'')')
go

exec sp_dboption 'UserDefinedForms','trunc. log on chkpt.','true'
exec sp_dboption 'UserDefinedForms','select into/bulkcopy','true'
GO

set quoted_identifier on
GO

/* Set DATEFORMAT so that the date strings are interpreted correctly regardless of
   the default DATEFORMAT on the server.
*/
SET DATEFORMAT mdy
GO
use "UserDefinedForms"
go
if exists (select * from sysobjects where id = object_id('dbo.DesignForms') and sysstat & 0xf = 3)
	drop table "dbo"."DesignForms"
GO
if exists (select * from sysobjects where id = object_id('dbo.SupOrderForms') and sysstat & 0xf = 3)
	drop table "dbo"."SupOrderForms"
GO
if exists (select * from sysobjects where id = object_id('dbo.FormData') and sysstat & 0xf = 3)
	drop table "dbo"."FormData"
GO
if exists (select * from sysobjects where id = object_id('dbo.FormControls') and sysstat & 0xf = 3)
	drop table "dbo"."FormControls"
GO
if exists (select * from sysobjects where id = object_id('dbo.ComponentSuppliers') and sysstat & 0xf = 3)
	drop table "dbo"."ComponentSuppliers"
GO
if exists (select * from sysobjects where id = object_id('dbo.ControlListItems') and sysstat & 0xf = 3)
	drop table "dbo"."ControlListItems"
GO
if exists (select * from sysobjects where id = object_id('dbo.Suppliers') and sysstat & 0xf = 3)
	drop table "dbo"."Suppliers"
GO
if exists (select * from sysobjects where id = object_id('dbo.Orders') and sysstat & 0xf = 3)
	drop table "dbo"."Orders"
GO
if exists (select * from sysobjects where id = object_id('dbo.ControlTypes') and sysstat & 0xf = 3)
	drop table "dbo"."ControlTypes"
GO
if exists (select * from sysobjects where id = object_id('dbo.Users') and sysstat & 0xf = 3)
	drop table "dbo"."Users"
GO



CREATE TABLE "Users" (
	"UserID" "int"  IDENTITY (1, 1)   NOT NULL ,
	"UserName" nvarchar(200) NOT NULL,
	"Type" "int" NOT NULL ,
	"Password" nvarchar(200) NOT NULL,
	"Status" "int" NOT NULL DEFAULT 1,
	CONSTRAINT "PK_Users" PRIMARY KEY  CLUSTERED 
	(
		"UserID"
	)
)
GO

CREATE TABLE "Suppliers" (
	"SupplierID" "int"  IDENTITY (1, 1)   NOT NULL ,
	"SupplierName" nvarchar(200) NOT NULL,
	"Status" "int" NOT NULL DEFAULT 1 ,
	CONSTRAINT "PK_Suppliers" PRIMARY KEY  CLUSTERED 
	(
		"SupplierID"
	)
)
GO


CREATE TABLE "ControlTypes" (
	"ControlTypeID" "int"  IDENTITY (1, 1)   NOT NULL ,
	"Name" nvarchar(50) NOT NULL,
	"Status" "int" NOT NULL DEFAULT 1 ,
	"Description" nvarchar(1000) NULL,
	CONSTRAINT "PK_ControlType" PRIMARY KEY  CLUSTERED 
	(
		"ControlTypeID"
	)
)
GO

CREATE TABLE "DesignForms" (
	"FormID" "int" IDENTITY (1, 1) NOT NULL ,
	"Name" nvarchar (200) NULL ,
	"Status" "int" NOT NULL DEFAULT 1,
	"Description" nvarchar(1000) NULL,
	"EffectiveDate" "datetime" NULL,
	"CreatedDate" "datetime" NULL,
	"ModifiedDate" "datetime" NULL,
	"UserID" "int" NOT NULL,
	"FormType" "int" NOT NULL,
	CONSTRAINT "PK_Forms" PRIMARY KEY  CLUSTERED 
	(
		"FormID"
	),
	CONSTRAINT "FK_UserForms" FOREIGN KEY 
	(
		"UserID"
	) REFERENCES "dbo"."Users" (
		"UserID"
	)
)
GO

CREATE TABLE "Orders" (
	"OrderID" "int"  IDENTITY (1, 1)   NOT NULL ,
	"OrderType" "int" NOT NULL,
	"FormID" "int" NOT NULL ,
	"CreatedDate" "datetime" NULL,
	CONSTRAINT "PK_Orders" PRIMARY KEY  CLUSTERED 
	(
		"OrderID"
	),
	CONSTRAINT "FK_OrderForm" FOREIGN KEY 
	(
		"FormID"
	) REFERENCES "dbo"."DesignForms" (
		"FormID"
	)
)
GO

CREATE TABLE "SupOrderForms" (
	"FormID" "int"  NOT NULL ,
	"Background" image NOT NULL ,
	"SupplierID" "int" NOT NULL ,
	CONSTRAINT "PK_SupForm" PRIMARY KEY  CLUSTERED 
	(
		"FormID"
	),
	CONSTRAINT "FK_SupFormDesignForms" FOREIGN KEY 
	(
		"FormID"
	) REFERENCES "dbo"."DesignForms" (
		"FormID"
	)
)
GO
 CREATE  INDEX "Supplier" ON "dbo"."SupOrderForms"("SupplierID")
GO

CREATE TABLE "FormControls" (
	"ControlID" "int" IDENTITY (1, 1) NOT NULL ,
	"Name" nvarchar(100)  NULL ,
	"ControlTypeID" "int" NOT NULL ,
	"ToolTipText" nvarchar(1000)  NULL ,
	"Border" "bit"  NULL ,
	"Top" "int"  NULL ,
	"Left" "int"  NULL ,
	"Width" "int"  NULL ,
	"Height" "int"  NULL ,
	"Font" nvarchar(200)  NULL ,
	"DefaultValue" nvarchar(200)  NULL ,
	"Visible" "bit"  NULL ,
	"Enable" "bit"  NULL ,
	"IsSummaryField" "bit"  NULL ,
	"IsCommon" "bit"  NULL ,
	"IsCode" "bit"  NULL ,
	"IsLayout" "bit"  NULL ,
	"IsHeader" "bit" NULL,
	"ComponentID" "int"  NULL ,
	"FormID" "int"  NOT NULL ,
	"FormType" "int" NOT NULL ,
	"Status" "int" NOT NULL  DEFAULT 1,
	"Description" nvarchar(1000)  NULL ,
	CONSTRAINT "PK_FormControl" PRIMARY KEY  CLUSTERED 
	(
		"ControlID"
	),
	CONSTRAINT "FK_ControlType" FOREIGN KEY 
	(
		"ControlTypeID"
	) REFERENCES "dbo"."ControlTypes" (
		"ControlTypeID"
	),
	CONSTRAINT "FK_ControlComponent" FOREIGN KEY 
	(
		"ComponentID"
	) REFERENCES "dbo"."FormControls" (
		"ControlID"
	),
	CONSTRAINT "FK_ControlForm" FOREIGN KEY 
	(
		"FormID"
	) REFERENCES "dbo"."DesignForms" (
		"FormID"
	)
)
GO
 CREATE  INDEX "ControlComponentIDX" ON "dbo"."FormControls"("ComponentID")
GO
 CREATE  INDEX "ControFormIDX" ON "dbo"."FormControls"("FormID")
GO

CREATE TABLE "FormData" (
	"DataID" "int" IDENTITY (1, 1) NOT NULL ,
	"Code" "int" NOT NULL ,
	"FormID" "int" NOT NULL ,
	"ControlID" "int" NOT NULL ,
	"Data" nvarchar(200) NOT NULL ,
	"SupOrderCode" "int" NULL,
	"FormType" "int" NOT NULL ,
	CONSTRAINT "PK_FormData" PRIMARY KEY  CLUSTERED 
	(
		"DataID"
	),
	CONSTRAINT "FK_CodeOrder" FOREIGN KEY 
	(
		"Code"
	) REFERENCES "dbo"."Orders" (
		"OrderID"
	),
	CONSTRAINT "FK_DataForm" FOREIGN KEY 
	(
		"FormID"
	) REFERENCES "dbo"."DesignForms" (
		"FormID"
	),
	CONSTRAINT "FK_DataControl" FOREIGN KEY 
	(
		"ControlID"
	) REFERENCES "dbo"."FormControls" (
		"ControlID"
	),
	CONSTRAINT "FK_DataSupOrder" FOREIGN KEY 
	(
		"SupOrderCode"
	) REFERENCES "dbo"."Orders" (
		"OrderID"
	)
)
GO
 CREATE  INDEX "DataCode" ON "dbo"."FormData"("Code")
GO
GO
 CREATE  INDEX "DataForm" ON "dbo"."FormData"("FormID")
GO
GO
 CREATE  INDEX "DataControl" ON "dbo"."FormData"("ControlID")
GO






CREATE TABLE "ComponentSuppliers" (
	"ControlID" "int" NOT NULL ,
	"SupplierID" "int" NOT NULL ,
	CONSTRAINT "PK_ComponentSupplier" PRIMARY KEY  CLUSTERED 
	(
		"ControlID","SupplierID"
	),
	CONSTRAINT "FK_ComponentSupplierControl" FOREIGN KEY 
	(
		"ControlID"
	) REFERENCES "dbo"."FormControls" (
		"ControlID"
	),
	CONSTRAINT "FK_ComponentSupplierSupplier" FOREIGN KEY 
	(
		"SupplierID"
	) REFERENCES "dbo"."Suppliers" (
		"SupplierID"
	)
)
GO




CREATE TABLE "ControlListItems" (
	"FormListID" "int"  IDENTITY (1, 1)   NOT NULL ,
	"ControlID" "int" NOT NULL,
	"ItemData" nvarchar(200) NOT NULL ,
	"Status" "int" NOT NULL DEFAULT 1,
	CONSTRAINT "PK_ControlListItems" PRIMARY KEY  CLUSTERED 
	(
		"FormListID"
	),
	CONSTRAINT "FK_ControlListItemControl" FOREIGN KEY 
	(
		"ControlID"
	) REFERENCES "dbo"."FormControls" (
		"ControlID"
	)
)
GO
 CREATE  INDEX "ControlListItemIDX" ON "dbo"."ControlListItems"("ControlID")
GO


